import numpy as np
import pandas as pd
from sklearn.feature_extraction.text import TfidfVectorizer
from sklearn.metrics.pairwise import cosine_similarity
from utils.db import get_mysql_conn, get_redis_conn
import json
from datetime import datetime

class ContentBasedRecommender:
    """基于内容的推荐算法"""
    
    def __init__(self):
        self.mysql_conn = get_mysql_conn()
        self.redis_conn = get_redis_conn()
        self.courses_df = None
        self.tfidf_matrix = None
        self.course_similarity = None
        self.course_id_map = {}
        self.reverse_course_map = {}
    
    def load_data_from_db(self):
        """从数据库加载课程数据"""
        print("从数据库加载课程数据...")
        
        try:
            if not self.mysql_conn:
                self.mysql_conn = get_mysql_conn()
                
            # 加载课程数据 - 适应现有的数据库结构
            query = """
            SELECT c.id, c.title, cat.name as category, c.tags, c.description, 
                   t.name as teacher_name, c.teacher_id
            FROM courses c
            LEFT JOIN categories cat ON c.category_id = cat.id
            LEFT JOIN teachers t ON c.teacher_id = t.id
            """
            
            self.courses_df = pd.read_sql(query, self.mysql_conn)
            
            # 处理空值
            text_columns = ['title', 'category', 'tags', 'description']
            for col in text_columns:
                if col in self.courses_df.columns:
                    self.courses_df[col] = self.courses_df[col].fillna('')
            
            # 添加一个keywords列（如果没有）
            if 'keywords' not in self.courses_df.columns:
                self.courses_df['keywords'] = self.courses_df['tags']
            
            print(f"从数据库加载了 {len(self.courses_df)} 门课程")
            return True
            
        except Exception as e:
            print(f"从数据库加载课程数据失败: {e}")
            return False
    
    def preprocess_data(self):
        """预处理课程数据，准备用于TF-IDF分析"""
        if self.courses_df is None:
            print("请先加载课程数据")
            return False
            
        print("预处理课程数据...")
        
        # 确保必要的列存在
        required_columns = ['id', 'title', 'category', 'tags', 'description']
        for col in required_columns:
            if col not in self.courses_df.columns:
                print(f"缺少必要的列: {col}")
                return False
        
        # 创建课程ID映射
        self.course_id_map = {id: idx for idx, id in enumerate(self.courses_df['id'])}
        self.reverse_course_map = {idx: id for id, idx in self.course_id_map.items()}
        
        # 组合课程特征文本，用于TF-IDF分析
        # 根据重要性加权，重复更重要的特征
        self.courses_df['content'] = (
            self.courses_df['title'] + ' ' + self.courses_df['title'] + ' ' +  # 标题重复两次
            self.courses_df['category'] + ' ' + self.courses_df['category'] + ' ' +  # 类别重复两次
            self.courses_df['tags'] + ' ' + self.courses_df['tags'] + ' ' +  # 标签重复两次
            self.courses_df['description'] + ' ' +  # 描述
            self.courses_df['keywords'].apply(lambda x: ' '.join([kw.strip() for kw in x.split(',')]) if x else '')  # 关键词
        )
        
        print("课程数据预处理完成")
        return True
    
    def build_tfidf_matrix(self):
        """构建TF-IDF矩阵"""
        if 'content' not in self.courses_df.columns:
            print("请先预处理课程数据")
            return False
            
        print("构建TF-IDF矩阵...")
        
        # 创建TF-IDF向量化器
        tfidf = TfidfVectorizer(
            analyzer='word',
            stop_words='english',  # 如果是英文内容
            ngram_range=(1, 2),    # 1-gram和2-gram
            min_df=2,              # 至少出现在2个文档中
            max_features=5000      # 最多5000个特征
        )
        
        # 转换课程内容为TF-IDF矩阵
        self.tfidf_matrix = tfidf.fit_transform(self.courses_df['content'])
        
        # 计算相似度矩阵
        self.course_similarity = cosine_similarity(self.tfidf_matrix)
        
        print(f"TF-IDF矩阵构建完成，大小: {self.tfidf_matrix.shape}")
        return True
    
    def save_similarity_to_redis(self):
        """将课程相似度保存到Redis"""
        if self.course_similarity is None:
            print("请先构建TF-IDF矩阵")
            return False
            
        print("保存内容相似度到Redis...")
        
        try:
            if not self.redis_conn:
                self.redis_conn = get_redis_conn()
                
            # 使用管道批量写入Redis
            pipeline = self.redis_conn.pipeline()
            
            # 设置过期时间（一天）
            expiry = 60 * 60 * 24
            
            # 记录更新时间
            update_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            pipeline.set("content_based:update_time", update_time)
            
            # 保存相似度矩阵
            for i, course_id in self.reverse_course_map.items():
                # 获取与该课程相似的其他课程及相似度
                similarities = {}
                for j, sim in enumerate(self.course_similarity[i]):
                    if i != j and sim > 0:  # 排除自身，只保存相似度大于0的
                        similarities[str(self.reverse_course_map[j])] = float(sim)
                
                # 对每个课程，只保存相似度最高的N个课程
                top_similar = sorted(similarities.items(), key=lambda x: x[1], reverse=True)[:50]
                
                # 将相似度转换为JSON格式并保存
                key = f"content_based:similarity:{course_id}"
                value = json.dumps(dict(top_similar))
                pipeline.set(key, value, ex=expiry)
            
            # 执行管道命令
            pipeline.execute()
            
            print(f"成功保存内容相似度到Redis，共 {len(self.reverse_course_map)} 个课程")
            return True
            
        except Exception as e:
            print(f"保存内容相似度到Redis失败: {e}")
            return False
    
    def load_similarity_from_redis(self):
        """从Redis加载课程内容相似度"""
        print("从Redis加载内容相似度...")
        
        try:
            if not self.redis_conn:
                self.redis_conn = get_redis_conn()
                
            # 获取更新时间
            update_time = self.redis_conn.get("content_based:update_time")
            if update_time:
                print(f"内容相似度数据的更新时间: {update_time}")
            
            # 获取所有课程相似度键
            similarity_keys = self.redis_conn.keys("content_based:similarity:*")
            
            if not similarity_keys:
                print("Redis中没有找到内容相似度数据")
                return False
            
            # 清空当前相似度矩阵
            self.content_similarity = {}
            
            # 加载相似度数据
            for key in similarity_keys:
                course_id = key.split(":")[-1]
                similarity_json = self.redis_conn.get(key)
                
                if similarity_json:
                    self.content_similarity[int(course_id)] = json.loads(similarity_json)
            
            print(f"从Redis加载了 {len(self.content_similarity)} 个课程的内容相似度数据")
            return True
            
        except Exception as e:
            print(f"从Redis加载内容相似度失败: {e}")
            return False
    
    def get_user_interests(self, user_id):
        """获取用户兴趣标签"""
        try:
            if not self.mysql_conn:
                self.mysql_conn = get_mysql_conn()
                
            # 查询用户感兴趣的内容 - 适应现有的数据库结构
            query = """
            SELECT c.id, c.title, cat.name as category, c.tags,
                   CASE 
                       WHEN b.event_type = 'rate' AND JSON_EXTRACT(b.extra_data, '$.rating') IS NOT NULL 
                       THEN CAST(JSON_EXTRACT(b.extra_data, '$.rating') AS DECIMAL(3,1))
                       WHEN b.event_type = 'complete' THEN 5.0
                       WHEN b.event_type = 'favorite' THEN 4.0
                       ELSE 3.0
                   END AS user_preference
            FROM user_behaviors b
            JOIN courses c ON b.course_id = c.id
            LEFT JOIN categories cat ON c.category_id = cat.id
            WHERE b.user_id = %s 
              AND (
                (b.event_type = 'rate' AND CAST(JSON_EXTRACT(b.extra_data, '$.rating') AS DECIMAL(3,1)) >= 4.0)
                OR b.event_type IN ('complete', 'favorite')
              )
            ORDER BY b.timestamp DESC
            LIMIT 10
            """
            
            user_courses = pd.read_sql(query, self.mysql_conn, params=[user_id])
            
            if len(user_courses) == 0:
                print(f"用户 {user_id} 没有找到足够的兴趣数据")
                return None
            
            # 提取用户的兴趣标签
            categories = []
            tags = []
            
            for _, course in user_courses.iterrows():
                if course['category']:
                    categories.append(course['category'])
                
                if course['tags']:
                    tags.extend([tag.strip() for tag in course['tags'].split(',')])
            
            # 统计频率
            from collections import Counter
            category_counter = Counter(categories)
            tag_counter = Counter(tags)
            
            # 提取最频繁出现的兴趣标签
            top_categories = [category for category, _ in category_counter.most_common(3)]
            top_tags = [tag for tag, _ in tag_counter.most_common(5)]
            
            # 组合用户兴趣标签
            user_interests = {
                'categories': top_categories,
                'tags': top_tags,
                'recent_courses': user_courses['id'].tolist()
            }
            
            return user_interests
            
        except Exception as e:
            print(f"获取用户兴趣标签失败: {e}")
            return None
    
    def recommend_by_content(self, user_id, n=10, exclude_courses=None):
        """基于内容为用户推荐课程"""
        # 初始化排除课程列表
        if exclude_courses is None:
            exclude_courses = []
        
        # 获取用户兴趣标签
        user_interests = self.get_user_interests(user_id)
        if not user_interests:
            print(f"无法获取用户 {user_id} 的兴趣标签")
            return []
        
        # 获取用户最近的课程
        recent_courses = user_interests['recent_courses']
        exclude_courses.extend(recent_courses)
        
        # 如果有内容相似度数据（从Redis加载的）
        if hasattr(self, 'content_similarity') and self.content_similarity:
            print("使用Redis中的内容相似度数据进行推荐")
            
            # 计算候选课程的分数
            candidate_scores = {}
            
            # 基于用户最近课程的相似课程推荐
            for course_id in recent_courses:
                if course_id in self.content_similarity:
                    similar_courses = self.content_similarity[course_id]
                    for similar_id, similarity in similar_courses.items():
                        if int(similar_id) not in exclude_courses:
                            if similar_id not in candidate_scores:
                                candidate_scores[similar_id] = 0
                            candidate_scores[similar_id] += similarity
            
            # 排序并返回推荐结果
            recommendations = sorted(candidate_scores.items(), key=lambda x: x[1], reverse=True)[:n]
            recommendations = [(int(course_id), score) for course_id, score in recommendations]
            
            return recommendations
        
        # 如果没有从Redis加载数据，使用TF-IDF矩阵进行推荐
        elif self.tfidf_matrix is not None:
            print("使用TF-IDF矩阵进行推荐")
            
            # 如果没有最近课程，无法推荐
            if not recent_courses:
                print("用户没有最近的课程，无法基于内容推荐")
                return []
            
            # 计算候选课程的分数
            candidate_scores = {}
            
            for course_id in recent_courses:
                if course_id in self.course_id_map:
                    idx = self.course_id_map[course_id]
                    similarities = self.course_similarity[idx]
                    
                    for j, sim in enumerate(similarities):
                        similar_id = self.reverse_course_map[j]
                        if similar_id not in exclude_courses and similar_id != course_id:
                            if similar_id not in candidate_scores:
                                candidate_scores[similar_id] = 0
                            candidate_scores[similar_id] += sim
            
            # 排序并返回推荐结果
            recommendations = sorted(candidate_scores.items(), key=lambda x: x[1], reverse=True)[:n]
            
            return recommendations
        
        # 如果没有相似度数据，使用兴趣标签匹配
        else:
            print("使用兴趣标签匹配进行推荐")
            
            # 从数据库查询匹配用户兴趣的课程
            if not self.mysql_conn:
                self.mysql_conn = get_mysql_conn()
            
            # 构建查询条件
            categories = user_interests['categories']
            tags = user_interests['tags']
            
            # 构建SQL查询
            category_condition = " OR ".join([f"category LIKE '%{cat}%'" for cat in categories]) if categories else "1=0"
            tag_condition = " OR ".join([f"tags LIKE '%{tag}%'" for tag in tags]) if tags else "1=0"
            exclude_condition = " AND ".join([f"id != {course_id}" for course_id in exclude_courses]) if exclude_courses else "1=1"
            
            query = f"""
            SELECT id, title, category, tags, 
                   (CASE WHEN ({category_condition}) THEN 2 ELSE 0 END) +
                   (CASE WHEN ({tag_condition}) THEN 1 ELSE 0 END) AS match_score
            FROM courses
            WHERE ({category_condition} OR {tag_condition}) AND {exclude_condition}
            ORDER BY match_score DESC, popularity DESC
            LIMIT {n}
            """
            
            matching_courses = pd.read_sql(query, self.mysql_conn)
            
            # 返回推荐结果
            recommendations = [(row['id'], row['match_score']) for _, row in matching_courses.iterrows()]
            
            return recommendations
    
    def train_model(self):
        """训练模型，计算内容相似度并保存到Redis"""
        # 从数据库加载数据
        if not self.load_data_from_db():
            print("加载数据失败，无法训练模型")
            return False
        
        # 预处理数据
        if not self.preprocess_data():
            print("预处理数据失败，无法训练模型")
            return False
        
        # 构建TF-IDF矩阵
        if not self.build_tfidf_matrix():
            print("构建TF-IDF矩阵失败，无法训练模型")
            return False
        
        # 保存相似度到Redis
        return self.save_similarity_to_redis()

# 测试代码
if __name__ == "__main__":
    # 创建基于内容的推荐器实例
    content_recommender = ContentBasedRecommender()
    
    # 尝试从Redis加载相似度数据
    if not content_recommender.load_similarity_from_redis():
        print("Redis中没有内容相似度数据，开始训练模型...")
        content_recommender.train_model()
    
    # 测试推荐功能
    user_id = 1  # 替换为实际用户ID
    recommendations = content_recommender.recommend_by_content(user_id, n=5)
    
    print(f"\n基于内容为用户 {user_id} 的推荐课程:")
    for course_id, score in recommendations:
        print(f"课程ID: {course_id}, 匹配分数: {score:.2f}") 